package com.ctcemti.mdm.project.tool.generatecode.service;

import com.ctcemti.mdm.common.exception.UnCanConnectException;
import com.ctcemti.mdm.common.util.JdbcUtil;
import com.ctcemti.mdm.project.tool.generatecode.dao.GenTableColumnMapper;
import com.ctcemti.mdm.project.tool.generatecode.entity.GenTableColumn;
import com.ctcemti.mdm.project.tool.generatecode.entity.JDBCProperies;
import com.ctcemti.mdm.project.tool.generatecode.util.Convert;
import com.ctcemti.mdm.project.tool.generatecode.entity.GenTableColumn;
import com.ctcemti.mdm.project.tool.generatecode.entity.JDBCProperies;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 业务字段 服务层实现
 *
 * @author bxf
 */
@Service
public class GenTableColumnServiceImpl implements IGenTableColumnService
{
	@Autowired
	private GenTableColumnMapper genTableColumnMapper;

	/**
     * 查询业务字段列表
     *
     * @param genTableColumn 业务字段信息
     * @return 业务字段集合
     */
	@Override
	public List<GenTableColumn> selectGenTableColumnListByTableId(GenTableColumn genTableColumn)
	{
	    return genTableColumnMapper.selectGenTableColumnListByTableId(genTableColumn);
	}

    /**
     * 新增业务字段
     *
     * @param genTableColumn 业务字段信息
     * @return 结果
     */
	@Override
	public int insertGenTableColumn(GenTableColumn genTableColumn)
	{
	    return genTableColumnMapper.insertGenTableColumn(genTableColumn);
	}

	/**
     * 修改业务字段
     *
     * @param genTableColumn 业务字段信息
     * @return 结果
     */
	@Override
	public int updateGenTableColumn(GenTableColumn genTableColumn)
	{
	    return genTableColumnMapper.updateGenTableColumn(genTableColumn);
	}

	/**
     * 删除业务字段对象
     *
     * @param ids 需要删除的数据ID
     * @return 结果
     */
	@Override
	public int deleteGenTableColumnByIds(String ids)
	{
		return genTableColumnMapper.deleteGenTableColumnByIds(Convert.toLongArray(ids));
	}
	/* *
	 * @Author bxf
	 * @Description 根据名称获取tablecolumns
	 * @Date 16:36 2019/11/13
	 * @param tableName
	 * @param jdbcProperies
	 * @return java.util.List<com.ctcemti.mdm.project.tool.generatecode.entity.GenTableColumn>
	 */
	@Override
	public List<GenTableColumn> selectDbTableColumnsByName(String tableName, JDBCProperies jdbcProperies) throws UnCanConnectException {
		String sql = "";
		if(jdbcProperies.getDatasourceType()==1){//mysql
			sql = "select column_name as columnName, (case when (is_nullable = 'no' and column_key != 'PRI') then '1' else '0' end) as isRequired, (case when column_key = 'PRI' then '1' else '0' end) as isPk, ordinal_position as sort, column_comment as columnComment, (case when extra = 'auto_increment' then '1' else '0' end) as isIncrement, column_type as columnType " +
					" from information_schema.columns where table_schema = (select database()) and table_name = (?) " +
					" order by ordinal_position";
			jdbcProperies.setDriver("com.mysql.cj.jdbc.Driver");
		}else if(jdbcProperies.getDatasourceType()==2){//oracle
			sql = "select lower(temp.column_name) as \"columnName\", " +
					" (case when (temp.nullable = 'N'  and  temp.constraint_type != 'P') then '1' else null end) as \"isRequired\", " +
					" (case when temp.constraint_type = 'P' then '1' else '0' end) as \"isPk\", " +
					" temp.column_id as \"sort\", " +
					" temp.comments as \"columnComment\", " +
					" (case when temp.constraint_type = 'P' then '1' else '0' end) as \"isIncrement\", " +
					" lower(temp.data_type) as \"columnType\" " +
					" from ( " +
					" select col.column_id, col.column_name,col.nullable, col.data_type, colc.comments, uc.constraint_type " +
					" , row_number() over (partition by col.column_name order by uc.constraint_type desc) as row_flg " +
					" from user_tab_columns col " +
					" left join user_col_comments colc on colc.table_name = col.table_name and colc.column_name = col.column_name " +
					" left join user_cons_columns ucc on ucc.table_name = col.table_name and ucc.column_name = col.column_name " +
					" left join user_constraints uc on uc.constraint_name = ucc.constraint_name " +
					" where col.table_name = upper(?) " +
					" ) temp " +
					" WHERE temp.row_flg = 1 " +
					" ORDER BY temp.column_id";
			jdbcProperies.setDriver("oracle.jdbc.driver.OracleDriver");
		}else{//sqlserver
			sql = "SELECT" +
					" col.isnullable as isRequired,"+
					" col.colorder AS sort," +
					" col.name AS columnName," +
					" convert(varchar(100), ISNULL( ep.[value], '' )) AS columnComment," +
					" t.name AS columnType," +
					" CASE" +
					" WHEN EXISTS (" +
					" SELECT 1" +
					" FROM" +
					" dbo.sysindexes si" +
					" INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id" +
					" AND si.indid = sik.indid" +
					" INNER JOIN dbo.syscolumns sc ON sc.id = sik.id" +
					" AND sc.colid = sik.colid" +
					" INNER JOIN dbo.sysobjects so ON so.name = si.name" +
					" AND so.xtype = 'PK'" +
					" WHERE" +
					" sc.id = col.id" +
					" AND sc.colid = col.colid" +
					" ) THEN" +
					" '1' ELSE '0'" +
					" END AS isPk" +
					" FROM" +
					" dbo.syscolumns col" +
					" LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype" +
					" INNER JOIN dbo.sysobjects obj ON col.id = obj.id" +
					" AND obj.xtype = 'U'" +
					" AND obj.status >= 0" +
					" LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id" +
					" LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id" +
					" AND col.colid = ep.minor_id" +
					" AND ep.name = 'MS_Description'" +
					" LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id" +
					" AND epTwo.minor_id = 0" +
					" AND epTwo.name = 'MS_Description'" +
					" WHERE obj.name = ?" +
					" ORDER BY col.colorder ";
			jdbcProperies.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		}
		Connection connection = null;
		List<GenTableColumn> genTableColumns = new ArrayList<GenTableColumn>();
		try {
			connection = JdbcUtil.getConnect(jdbcProperies);
			genTableColumns = JdbcUtil.selectTableColumnsData(connection,sql,tableName);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new UnCanConnectException("无法连接数据库！");
		}
		return genTableColumns;
	}
}